// li_han/dao/PingLunDao.java
package com.example.li_han.dao;

import com.example.li_han.pojo.PingLun;
import com.example.li_han.utils.ConnUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PingLunDao {

    /**
     * 获取待审核评论
     */
    public List<PingLun> getPendingComments(int currentPage, int pageSize) {
        int start = (currentPage - 1) * pageSize;
        String sql = "SELECT p.*, n.newsTitle, u.zhangHao as userZhangHao " +
                "FROM pingLun p " +
                "LEFT JOIN news n ON p.newsId = n.newsId " +
                "LEFT JOIN user u ON p.userZhangHao = u.zhangHao " +
                "WHERE p.status = 0 " +
                "ORDER BY p.pingLunTime DESC LIMIT ?, ?";

        return getCommentsBySql(sql, start, pageSize);
    }

    /**
     * 添加评论
     */
    public int addPingLun(PingLun pingLun) {
        String sql = "INSERT INTO pingLun(userZhangHao, newsId, pingLunContent, pingLunTime, status) VALUES(?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = ConnUtils.getConn();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, pingLun.getUserZhangHao());
            pstmt.setInt(2, pingLun.getNewsId());
            pstmt.setString(3, pingLun.getPingLunContent());
            pstmt.setTimestamp(4, new java.sql.Timestamp(pingLun.getPingLunTime().getTime()));
            pstmt.setInt(5, pingLun.getStatus());

            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("添加评论失败：" + e.getMessage());
        } finally {
            closeResources(conn, pstmt, null);
        }
    }

    /**
     * 获取已审核评论
     */
    public List<PingLun> getApprovedComments(int currentPage, int pageSize) {
        int start = (currentPage - 1) * pageSize;
        String sql = "SELECT p.*, n.newsTitle, u.zhangHao as userZhangHao " +
                "FROM pingLun p " +
                "LEFT JOIN news n ON p.newsId = n.newsId " +
                "LEFT JOIN user u ON p.userZhangHao = u.zhangHao " +
                "WHERE p.status = 1 " +
                "ORDER BY p.pingLunTime DESC LIMIT ?, ?";

        return getCommentsBySql(sql, start, pageSize);
    }

    /**
     * 获取所有评论
     */
    public List<PingLun> getAllComments(int currentPage, int pageSize) {
        int start = (currentPage - 1) * pageSize;
        String sql = "SELECT p.*, n.newsTitle, u.zhangHao as userZhangHao " +
                "FROM pingLun p " +
                "LEFT JOIN news n ON p.newsId = n.newsId " +
                "LEFT JOIN user u ON p.userZhangHao = u.zhangHao " +
                "ORDER BY p.pingLunTime DESC LIMIT ?, ?";

        return getCommentsBySql(sql, start, pageSize);
    }

    /**
     * 根据SQL查询评论
     */
    private List<PingLun> getCommentsBySql(String sql, int start, int pageSize) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<PingLun> comments = new ArrayList<>();

        try {
            conn = ConnUtils.getConn();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, start);
            pstmt.setInt(2, pageSize);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                PingLun comment = new PingLun();
                comment.setPingLunId(rs.getInt("pingLunId"));
                comment.setNewsId(rs.getInt("newsId"));
                comment.setNewsTitle(rs.getString("newsTitle"));
                comment.setUserZhangHao(rs.getString("userZhangHao"));
                comment.setPingLunContent(rs.getString("pingLunContent"));
                comment.setPingLunTime(rs.getTimestamp("pingLunTime"));
                comment.setStatus(rs.getInt("status"));
                comment.setAdminRemark(rs.getString("adminRemark"));
                comments.add(comment);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询评论失败：" + e.getMessage());
        } finally {
            closeResources(conn, pstmt, rs);
        }
        return comments;
    }

    /**
     * 审核评论
     */
    public int approveComment(int pingLunId, int status, String adminRemark) {
        String sql = "UPDATE pingLun SET status = ?, adminRemark = ? WHERE pingLunId = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = ConnUtils.getConn();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, status);
            pstmt.setString(2, adminRemark);
            pstmt.setInt(3, pingLunId);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("审核评论失败：" + e.getMessage());
        } finally {
            closeResources(conn, pstmt, null);
        }
    }

    /**
     * 批量审核评论
     */
    public int batchApproveComments(List<Integer> ids, int status, String adminRemark) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int count = 0;

        try {
            conn = ConnUtils.getConn();
            conn.setAutoCommit(false);

            String sql = "UPDATE pingLun SET status = ?, adminRemark = ? WHERE pingLunId = ?";
            pstmt = conn.prepareStatement(sql);

            for (int id : ids) {
                pstmt.setInt(1, status);
                pstmt.setString(2, adminRemark);
                pstmt.setInt(3, id);
                count += pstmt.executeUpdate();
            }

            conn.commit();
            return count;
        } catch (SQLException e) {
            try {
                if (conn != null) conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            throw new RuntimeException("批量审核评论失败：" + e.getMessage());
        } finally {
            closeResources(conn, pstmt, null);
        }
    }

    /**
     * 删除评论
     */
    public int deleteComment(int pingLunId) {
        String sql = "DELETE FROM pingLun WHERE pingLunId = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = ConnUtils.getConn();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, pingLunId);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("删除评论失败：" + e.getMessage());
        } finally {
            closeResources(conn, pstmt, null);
        }
    }

    /**
     * 批量删除评论
     */
    public int batchDeleteComments(List<Integer> ids) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int count = 0;

        try {
            conn = ConnUtils.getConn();
            conn.setAutoCommit(false);

            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < ids.size(); i++) {
                sb.append("?");
                if (i < ids.size() - 1) sb.append(",");
            }

            String sql = "DELETE FROM pingLun WHERE pingLunId IN (" + sb + ")";
            pstmt = conn.prepareStatement(sql);

            for (int i = 0; i < ids.size(); i++) {
                pstmt.setInt(i + 1, ids.get(i));
            }

            count = pstmt.executeUpdate();
            conn.commit();
            return count;
        } catch (SQLException e) {
            try {
                if (conn != null) conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            throw new RuntimeException("批量删除评论失败：" + e.getMessage());
        } finally {
            closeResources(conn, pstmt, null);
        }
    }

    /**
     * 获取评论总数
     */
    public int getCommentCount(int status) {
        String sql = status == -1 ?
                "SELECT COUNT(*) FROM pingLun" :
                "SELECT COUNT(*) FROM pingLun WHERE status = ?";

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = ConnUtils.getConn();
            pstmt = conn.prepareStatement(sql);

            if (status != -1) {
                pstmt.setInt(1, status);
            }

            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
            return 0;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("获取评论数量失败：" + e.getMessage());
        } finally {
            closeResources(conn, pstmt, rs);
        }
    }

    /**
     * 关闭数据库资源
     */
    private void closeResources(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}